package org.genew.squadra.xml.business.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import org.genew.squadra.xml.model.auth.Role;
import org.genew.squadra.xml.model.auth.User;
import org.genew.squadra.xml.model.auth.myLoginForm;
import org.genew.squadra.xml.model.epubbiblio.Epub;

import javax.sql.DataSource;

import org.genew.squadra.xml.business.RequestGrid;
import org.genew.squadra.xml.business.ResponseGrid;
import org.genew.squadra.xml.business.ServiceBusiness;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class EpubService extends ServiceBusiness {

	 @Autowired
	 private DataSource dataSource;
	 
	 
	 public User authenticate(String u) throws Exception {
			Connection con = null;
			PreparedStatement st = null;
			ResultSet rs = null;
			User result = null;
			try {
				con = dataSource.getConnection();
				st = con.prepareStatement("select * from user u where u.username = ?");
				st.setString(1, u);
				System.out.println("");
				rs = st.executeQuery();
				if (rs.next()) {
					String username = rs.getString("Username");
					String password = rs.getString("Password");
					String firstName = rs.getString("Nome");
					String lastName = rs.getString("Cognome");
					Set<Role> roles = findRoles(u, con);
					//Set<Role> roles = null;
					result = new User(username, password, firstName, lastName, roles);
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception(e);
			} finally {
				if (st != null) {
					try {
						st.close();
					} catch (SQLException e) {
					}
				}
				if (con != null) {
					try {
						con.close();
					} catch (SQLException e) {
					}
				}

			}
			return result;
		}

		private Set<Role> findRoles(String u, Connection con) throws Exception {
			PreparedStatement st = null;
			ResultSet rs = null;
			Set<Role> result = new HashSet<Role>();
			try {
				st = con.prepareStatement("select r.* from user_role ur, roles r where ur.username = ? and ur.id_role = r.id");
				st.setString(1, u);
				rs = st.executeQuery();
				while (rs.next()) {
					String roleName = rs.getString("nome");
					String description = rs.getString("descrizione");
					Role role = new Role();
					role.setName(roleName);
					role.setDescription(description);
					result.add(role);
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception(e);
			} finally {
				if (st != null) {
					try {
						st.close();
					} catch (SQLException e) {
					}
				}
			}
			return result;
		}

		public boolean insertUser(User user) {
			Connection con = null;
			PreparedStatement st = null;
			int rs;
			boolean result = false;
			try {
				con = dataSource.getConnection();
				String query = "INSERT INTO USER"
						+ "(NOME, COGNOME, USERNAME, PASSWORD) VALUES"
						+ "(?,?,?,?)";
				st = con.prepareStatement(query);
				st.setString(1, user.getFirstName());
				st.setString(2, user.getLastName());
				st.setString(3, user.getUsername());
				st.setString(4, user.getPassword());
				rs = st.executeUpdate();
				System.out.println("=====riga inserita");
				String query2 = "INSERT INTO USER_ROLE"
						+ "(USERNAME, ID_ROLE) VALUES"
						+ "(?,?)";
				st.close();
				st = null;
				rs=0;
				st = con.prepareStatement(query2);
				st.setString(1, user.getUsername());
				st.setInt(2, 3);
				rs = st.executeUpdate();
				System.out.println("=====riga inserita"+rs);
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
				try {
					throw new Exception(e);
				} catch (Exception e1) {
					
					e1.printStackTrace();
				}
				
				
			} finally {
				if (st != null) {
					try {
						st.close();
					} catch (SQLException e) {
					}
				}
			}
			
		
		return result;
	
}
}
